using System;
using System.Linq;
using System.Collections.Generic;
using Dapper;
using MediatR;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.SalesManagement.H;
using RBAC.Read.Api.Application.Dto.ERP.SalesManagement.H;
using static RBAC.ErrorCode.APIEnums;

namespace RBAC.Read.Api.Application.Handler.ERP.SalesManagement.H
{
    /// <summary>
    /// 发货通知单所有数据联查处理器
    /// </summary>
    public class GetDeliveryNoticeAllDataCommandHandler : IRequestHandler<GetDeliveryNoticeAllDataCommand, APIResult<List<DeliveryNoticeAllDataDto>>>
    {
        private readonly IDbConnectionFactory _dbConnectionFactory;

        public GetDeliveryNoticeAllDataCommandHandler(IDbConnectionFactory dbConnectionFactory)
        {
            _dbConnectionFactory = dbConnectionFactory;
        }

        public async Task<APIResult<List<DeliveryNoticeAllDataDto>>> Handle(GetDeliveryNoticeAllDataCommand request, CancellationToken cancellationToken)
        {
            try
            {
                using var conn = _dbConnectionFactory.CreateConnection();

                var where = new List<string>();
                var p = new DynamicParameters();

                // 基础：仅查询未删除
                where.Add("dn.IsDeleted = 0");
                where.Add("(dni.IsDeleted = 0 OR dni.Id IS NULL)");

                if (!string.IsNullOrWhiteSpace(request.SalesOrderCode))
                {
                    where.Add("dn.Sales_order_code LIKE @SalesOrderCode");
                    p.Add("@SalesOrderCode", $"%{request.SalesOrderCode.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.BillCode))
                {
                    where.Add("dn.Bill_code LIKE @BillCode");
                    p.Add("@BillCode", $"%{request.BillCode.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.CustomerName))
                {
                    // 优先从发货通知单已存的客户名，如果导出查询里是 Customer 表，也可切换
                    where.Add("c.CustomerName LIKE @CustomerName");
                    p.Add("@CustomerName", $"%{request.CustomerName.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.SalesmanName))
                {
                    where.Add("u.UserName LIKE @SalesmanName");
                    p.Add("@SalesmanName", $"%{request.SalesmanName.Trim()}%");
                }
                if (!string.IsNullOrWhiteSpace(request.MaterialName))
                {
                    // 明细或 Mater 上的物料名
                    where.Add("(dni.Material_name LIKE @MaterialName OR m.MaterName LIKE @MaterialName)");
                    p.Add("@MaterialName", $"%{request.MaterialName.Trim()}%");
                }
                if (request.Status.HasValue)
                {
                    where.Add("dn.Status = @Status");
                    p.Add("@Status", request.Status.Value);
                }
                if (!string.IsNullOrWhiteSpace(request.BizType))
                {
                    where.Add("dn.Biz_type = @BizType");
                    p.Add("@BizType", request.BizType);
                }
                if (request.StartDate.HasValue)
                {
                    where.Add("dn.Bill_date >= @StartDate");
                    p.Add("@StartDate", request.StartDate.Value.Date);
                }
                if (request.EndDate.HasValue)
                {
                    where.Add("dn.Bill_date <= @EndDate");
                    p.Add("@EndDate", request.EndDate.Value.Date.AddDays(1).AddSeconds(-1));
                }

                var whereClause = where.Count > 0 ? ("WHERE " + string.Join(" AND ", where)) : string.Empty;

                var sql = $@"
SELECT DISTINCT
  dn.Id                                   AS Id,
  dn.Id                                   AS DeliveryNoticeId,
  dn.Bill_code                             AS BillCode,
  dn.Sales_order_code                      AS SalesOrderCode,
  c.CustomerName                           AS CustomerName,
  u.UserName                               AS SalesmanName,
  w.WarehouseName                          AS WarehouseName,
  dn.Receiver                              AS Receiver,
  dn.Phone                                 AS Phone,
  dn.Address                               AS Address,
  dn.Biz_type                              AS BizType,
  dn.Status                                AS Status,
  CASE dn.Status WHEN 0 THEN '开立' WHEN 1 THEN '审核' WHEN 2 THEN '退回' ELSE '未知' END AS StatusDescription,
  dn.Bill_date                             AS BillDate,
  dn.Delivery_date                         AS DeliveryDate,
  dn.Remark                                AS Remark,
  dn.Audit_user_id                         AS AuditUserId,
  dn.Audit_time                            AS AuditTime,
  dni.Id                                   AS DeliveryNoticeItemId,
  NULLIF(dni.Material_id, 0)               AS MaterialId,
  COALESCE(dni.Material_code, m.MaterCode) AS MaterialCode,
  COALESCE(dni.Material_name, m.MaterName) AS MaterialName,
  COALESCE(dni.Unit, m.Unit)               AS Unit,
  dni.Qty                                  AS Qty,
  dni.Package_qty                          AS PackageQty,
  dni.Batch_no                             AS BatchNo,
  so.Id                                    AS SalesOrderId,
  sod.Id                                   AS SalesOrderDetailId,
  sod.Num                                  AS SalesOrderQty,
  sod.Price                                AS SalesOrderPrice,
  (CASE WHEN sod.Num IS NOT NULL AND sod.Price IS NOT NULL THEN sod.Num * sod.Price ELSE NULL END) AS SalesOrderAmount,
  m.ModelSpec                              AS MaterModelSpec,
  mt.TypeName                              AS MaterTypeName,
  mt.TypeCode                              AS MaterTypeCode
FROM Delivery_notice dn
LEFT JOIN Customer c ON dn.Customer_id = c.Id AND c.IsDeleted = 0
LEFT JOIN User u ON dn.Salesman_id = u.Id AND u.IsDeleted = 0
LEFT JOIN WarehouseList w ON dn.Warehouse_id = w.Id AND w.IsDeleted = 0
LEFT JOIN Delivery_notice_item dni ON dn.Id = dni.Delivery_notice_id AND (dni.IsDeleted = 0 OR dni.Id IS NULL)
LEFT JOIN Mater m ON (dni.Material_id = m.Id OR (dni.Material_code IS NOT NULL AND dni.Material_code = m.MaterCode)) AND m.IsDeleted = 0
LEFT JOIN MaterType mt ON m.MaterTypeId = mt.Id AND mt.IsDeleted = 0
LEFT JOIN SalesOrder so ON so.OrderCode = dn.Sales_order_code AND so.IsDeleted = 0
LEFT JOIN SalesOrderDetail sod ON sod.SalesOrderId = so.Id AND m.Id IS NOT NULL AND sod.MaterId = m.Id AND sod.IsDeleted = 0
{whereClause}
ORDER BY dn.Bill_date DESC, dn.Id DESC, dni.Id ASC";

                var rows = await conn.QueryAsync<DeliveryNoticeAllDataDto>(sql, p);

                return new APIResult<List<DeliveryNoticeAllDataDto>>
                {
                    Code = Success,
                    Msg = "查询成功",
                    Data = rows.ToList()
                };
            }
            catch (Exception ex)
            {
                return new APIResult<List<DeliveryNoticeAllDataDto>>
                {
                    Code = Error,
                    Msg = $"查询失败: {ex.Message}",
                    Data = default
                };
            }
        }
    }
}
